﻿import sys
# import pandas as pd
from pandas import DataFrame
from pandas import concat, read_excel, ExcelWriter, ExcelFile, isna
# sys.path.append('D:\Program Files\Python\Lib\site-packages')
import xlwings as xw
import PySimpleGUI as sg
import os
import pyperclip


def GUI():
    pass


# 查找数值
def lookfor_value(df, key, n=0):
    '''
    查找指定文本或数字,返回其所在行列号的列表，如[[1,2],[2,3]]\n
    n为前几位,如n=3时，[[1,2],[3,4],[5,6]],默认n=0为全部\n
    行列号从零开始，文本为包含关系，数字为相等关系
    '''
    place = []  # 座标列表
    h = 0  # 行号
    num = 0  # 列号
    for i in df.values:
        l = 0
        for j in i:
            # print(j)
            if type(key) == float or type(key) == int:
                if key == j:
                    num += 1
                    place.append([h, l])
            else:
                if key in str(j):
                    num += 1
                    place.append([h, l])
            if n == 0:
                pass
            elif num >= n:
                return place
            l += 1
        h += 1
    # print(place)
    return place


# 过滤文件
def get_excel_list(all_file_list, file_key: str):
    '''
    在all_file_list中，找到含有file_key的文件,返回文件列表
    '''
    excel_list = []
    try:
        file_key = str(file_key)
    except:
        print('文件类型应当是字符\n')
        return 0
    for f in all_file_list:
        if file_key in f:
            if '~$' not in f:  # 去除临时文件
                excel_list.append(f)
    return excel_list


# 获取路径下，及其所有子目录下的所有文件
def get_all_path(file_dir):
    '''
    查找file_dir下的所有文件，以及文件夹下的文件，返回文件路径列表
    '''
    all_file = []
    for root, dirs, files in os.walk(file_dir):
        # print('root_dir:', root)  # 当前目录路径
        # print('sub_dirs:', dirs)  # 当前路径下所有子目录
        # print('files:', files)  # 当前路径下所有非目录子文件
        for file in files:
            path = root+'\\'+file
            # print(path)
            all_file.append(path)
    return all_file


class excel_tool():
    def __init__(self):
        self.pos = 'A2'
        self.label = ['姓名', 'ID']
        self.quality = '数量'
        self.gather_GUI()

    # 分类汇总,并将结果保存在相邻的新建工作表中
    def gather(self, rng_start: str, total: str, params: list):
        '''
        分类汇总,并将结果保存在相邻的新建工作表中\n
        rng_start:起始单元格,应当在"列标签+数据"所在区域的左上角第一个单元格,例如"A1"\n
        total:需要汇总的列的标签,例如"数量"\n
        params:列标签构成的列表,例如["姓名","ID"],即代表,只有姓名和ID都相同,才会进行汇总\n
        '''
        sht = xw.sheets.active  # 获取当前已经打开的sheet
        wb = xw.Book.activate  # 获取当前已打开的workbook

        df = sht.range(rng_start, sht.used_range.shape)
        # 第一行作为标签，后面作为dataframe
        df = DataFrame(df.value[1:], columns=df.value[0])

        try:
            # 如果标签或统计列中有Nan值，会无法统计
            df_lables = df[params].fillna(' ').astype("str")
            df_results = df[total].fillna(0).astype("float")
            df = concat([df_lables, df_results], axis=1)  # 横向合并标签和汇总值
        except KeyError:
            print('出现错误,请检查关键字是否和表格一致\n已中止\n')
            return 0

        # 分类汇总,注意,如果不加as_index=False，那么params会变成标签列，无法进行切片等操作
        result = df.groupby(by=params, as_index=False).sum()
        # result=result.apply(lambda x: x) # 用于as_index=True时，将groupby的结果的标签转为普通列表

        try:
            # 新建一个sheet,名字为汇总数据源表的名字+'汇总'
            total_sheet = xw.sheets.add(sht.name+'汇总', after=sht.name)
        except ValueError:
            total_sheet = xw.sheets.add(after=sht.name)

        total_sheet.range(rng_start).value = result  # 写入数据

        total_sheet.range(rng_start).options(transpose=True).value = [
            '序号']+[str(i) for i in range(1, result.shape[0]+1)]  # 写入序号列(写入dateframe时,第一列原本为index,覆盖为序号列)
        # total_sheet.range('A:A').api.delete  #调用API删除A列,备忘
        return '在"{}"表后生成了汇总表"{}"\n已完成'.format(sht.name, total_sheet.name)

    # 读取GUI的参数
    def read_gather_GUI(self, values: dict):
        print(values)
        self.pos = values['pos']
        self.label = values['label'].replace('，', ',').split(',')
        self.quality = values['quality']

    def gather_GUI(self):
        # 设置界面
        layout = [[sg.Text('分类汇总', size=(25, 1), justification='center')],
                  [sg.Text('标签起点', size=(25, 1)), sg.InputText(
                      default_text='A2', size=(10, 1), key='pos')],
                  [sg.Text('用于分类的标签，多个标签以逗号隔开', size=(25, 1)), sg.InputText(
                      default_text='姓名,ID', size=(15, 1), key='label')],
                  [sg.Text('想要汇总的量', size=(25, 1)), sg.InputText(
                      default_text='数量', size=(11, 1), key='quality')],
                  [sg.Button('点击进行分类汇总', size=(25, 1), pad=(35, 1))]
                  ]

        # 创建一个窗口
        window = sg.Window('分类汇总工具', layout)
        # Event Loop to process "events" and get the "values" of the inputs
        while True:
            event, values = window.read()
            if event in (None, 'Cancel'):   # 当点击关闭或取消按钮时
                break
            self.read_gather_GUI(values)  # 获取GUI界面上的参数

            # 开始运行
            # app=xw.App.activate
            result = self.gather(self.pos, self.quality, self.label)
            sg.PopupOK(result)  # 完成后出现ok按钮
        # window.close()


class combine_excel():
    def __init__(self):
        self.file_path = os.getcwd()
        self.combine_mode = 0
        self.key = '序号'
        self.combine_GUI()

    def trans_xlsx(self, xls_path):
        excel_list = [x for x in os.listdir(xls_path) if '.xls' in x]
        xls_list = [x for x in excel_list if '.xlsx' not in x]
        # print(xls_list)
        if len(xls_list) == 0:
            # sg.PopupOK('当前目录未发现xls文件')
            return 0
        for xls in xls_list:
            df_dict = read_excel(
                xls_path+'\\'+xls, sheet_name=None, header=None)
            # writer=ExcelWriter(xls+'x',engine='openpyxl')
            writer = ExcelWriter(xls+'x')
            for name in df_dict.keys():
                df_dict[name].to_excel(writer, name, header=None, index=None)
            writer.save()
            os.remove(xls_path+'\\'+xls)
        # sg.Popup_ok('已转化了%s个文件,分别是%s' %(len(xls_list),str(xls_list)[1:-1]))

    def getTags(self, df, h, l):
        '''
        h，l为起始标签所在行、列（从0开始）,返回标签名列表和"nan"的次数
        '''
        content = []
        nan_ci = 0
        for x in df.iloc[h+1:df.shape[0], l]:
            if not isna(x):
                # print('key下面的nan行数是 %s' % nan_ci)
                break
            nan_ci += 1
        for x in range(df.shape[1]):  # 遍历所有列
            weizhi = ''  # 每列初始为空
            for y in range(nan_ci+1):
                if not isna(df.iloc[h+y, x]):
                    weizhi = weizhi+str(df.iloc[h+y, x])
            content.append(weizhi)
        # print(content)
        return content, nan_ci

    def easy_combine_excel(self, excel_list):
        excels = []
        for excel in excel_list:
            data = ExcelFile(excel)
            sheetnames = data.sheet_names
            if self.combine_mode == 0:
                for sheet in sheetnames:
                    excels.append(read_excel(
                        excel, sheet_name=sheet, header=None))
            else:
                excels.append(read_excel(
                    excel, sheet_name=sheetnames[self.combine_mode-1], header=None))
        df = concat(excels)
        df.to_excel('合并结果.xlsx', index=False)
        sg.PopupOK('已简单合并完成，请查看合并结果')

    def combine_excel(self, excel_path, combine_mode):
        '''
        params:excel_path  excel所在路径
        params:combine_mode 0或n，0为全部sheet，n为第n个sheet
        '''
        os.chdir(excel_path)
        self.trans_xlsx(excel_path)
        excel_list = [x for x in os.listdir() if '.xlsx' in x]
        excels = []
        for excel in excel_list:
            print(excel)
            data = ExcelFile(excel)

            # 获取tags和行列
            # df = self.excel_to_df(data)
            # hang, lie = lookfor_value(df, self.key)
            # tags, time_of_Nan = self.getTags(df, hang, lie)

            sheetnames = data.sheet_names
            if combine_mode == 0:
                for sheet in sheetnames:
                    df = read_excel(excel, sheet_name=sheet, header=None)
                    try:
                        [hang, lie] = lookfor_value(df, self.key, n=1)[0]
                    except:
                        hang = lie = None
                    if hang == 0 or hang:
                        pass
                    else:
                        print("未找到'%s'" % self.key)
                        sg.PopupOK("未找到'%s',即将转换为不含关键字的简单合并模式\n" % self.key)
                        # sys.exit(0)
                        return self.easy_combine_excel(excel_list)

                    tags, time_of_Nan = self.getTags(df, hang, lie)

                    df = read_excel(
                        excel, sheet_name=sheet, skiprows=hang+time_of_Nan+1, header=None, names=tags)
                    df['数据_来源'] = '|'+excel+'|\\|'+sheet+'|'
                    excels.append(df)
            else:
                # print(sheetnames[self.combine_mode-1])
                df = read_excel(
                    excel, sheet_name=sheetnames[self.combine_mode-1], header=None)
                [hang, lie] = lookfor_value(df, self.key, n=1)[0]
                if hang:
                    pass
                else:
                    print("未找到'%s'" % self.key)
                    sg.PopupOK("未找到'%s',即将转换为不含关键字的简单合并模式\n" % self.key)
                    # sys.exit(0)
                    return self.easy_combine_excel(excel_list)

                tags, time_of_Nan = self.getTags(df, hang, lie)

                df = read_excel(
                    excel, sheet_name=sheetnames[self.combine_mode-1], skiprows=hang+time_of_Nan+1, header=None, names=tags)
                df['数据_来源'] = '|'+excel+'|\\|' + \
                    sheetnames[self.combine_mode-1]+'|'
                excels.append(df)
        df = concat(excels, sort=False)
        df.to_excel('合并结果.xlsx', index=False)
        sg.PopupOK('已合并完成，请查看合并结果')

    def combine_GUI(self):
        layout = [[sg.Text('选择想操作的excel文件所在的文件夹')],
                  [sg.Text('请选择路径', size=(20, 1)), sg.InputText(
                      default_text=self.file_path, key='file_path'), sg.FolderBrowse()],
                  [sg.Text('合并第几个sheet(0为全部sheet)', size=(20, 1)), sg.InputText(
                      default_text=self.combine_mode, key='combine_mode')],
                  [sg.Text('数据标签的起始关键字', size=(20, 1)), sg.InputText(
                      default_text=self.key, key='key')],
                  [sg.Button('点击进行合并', size=(25, 1), pad=(35, 1))]
                  ]

        window = sg.Window('转化与合并excel文件', layout)
        while True:
            event, values = window.read()
            if event in (None, 'Cancel'):   # 当点击关闭或取消按钮时
                break
            self.read_gather_GUI(values)
            self.combine_excel(self.file_path, self.combine_mode)
        window.close()

    # 读取GUI的参数

    def read_gather_GUI(self, values: dict):
        # print(values)
        self.file_path = values['file_path']
        self.combine_mode = int(values['combine_mode'])
        try:
            self.key = float(values['key'])
        except:
            self.key = str(values['key'])


class lookup_excel():
    def __init__(self) -> None:
        self.file_path = os.getcwd()
        self.key = '关键字'
        self.lookup_GUI(self.main)


    def get_res(self):
        print('程序启动\n')
        # key=input('请输入需要查找的文本\n')
        # key = 22400
        # path = r'F:\靳骋\航天\人工费\2020.5结算'
        all_file_list = get_all_path(self.file_path)
        excel_list = get_excel_list(all_file_list, 'xlsx')
        res = []
        num = 0  # 找到的第num个
        for excel_file in excel_list:
            data = ExcelFile(excel_file)
            sheetnames = data.sheet_names
            for sheet in sheetnames:
                df = read_excel(excel_file, sheet_name=sheet, header=None)
                place = lookfor_value(df, self.key)
                if place:
                    for p in place:
                        num += 1
                        res.append([num,excel_file, sheet, p])   
        return res



    def get_info(self,res):
        if res == []:
            return None
        else:
            info = ''
            for i in range(len(res)):
                r=res[i]
                print(r)
                name = '|{}|\\|{}|'.format(r[1], r[2])       
                new_word = '({})  在 {} 表的第{}行第{}列\n'.format(r[0], name, r[3][0]+1,r[3][1]+1)
                info = info+new_word                      
            new_word = '关键字：{}\n共找到{}处\n\n'.format(self.key, r[0])    
            info=new_word+info                         
        return info


    def main(self):
        res=self.get_res()
        info=self.get_info(res)
        return info


    def read_GUI(self, values):
        self.file_path = values['path']
        try:
            self.key = float(values['key'])
        except:
            self.key = values['key']


    def lookup_GUI(self,fun):
        layout = [[sg.Text('选择路径和搜索的关键字')],
                  [sg.Text('请选择路径', size=(20, 1)), sg.Input(
                      default_text='', key='path'), sg.FolderBrowse()],
                  [sg.Text('关键字', size=(20, 1)), sg.InputText(
                      key='key')],
                  [sg.Button('点击进行搜索', size=(25, 1), pad=(35, 1))],
                  [sg.Multiline('搜索结果显示处', key='_Multiline_', size=(
                      120, 10), autoscroll=False, do_not_clear=False)],
                  [sg.Button('点此复制结果', size=(10, 1), pad=(35, 1))]
                  ]
        # 创建一个窗口
        window = sg.Window('excel内容搜索工具', layout)
        # Event Loop to process "events" and get the "values" of the inputs
        while True:
            event, values = window.read()
            if event in (None, 'Cancel'):   # 当点击关闭或取消按钮时
                break
            elif event in ('点击进行搜索'):
                self.read_GUI(values)
                # 开始运行
                info = fun()
                if info:
                    sg.PopupOK("搜索完毕,可查看结果")
                    Multiline_txt = info
                    window.Element('_Multiline_').Update(Multiline_txt)
                else:
                    sg.PopupOK('搜索完毕，未找到"{}"'.format(self.key))
            elif event in ('点此复制结果'):
                info = values['_Multiline_']
                pyperclip.copy(info)
                sg.PopupOK('已复制到剪贴板，可以粘贴到文件中显示更方便')
        # window.close()


if __name__ == '__main__':
    sg.theme('DarkAmber')    # 主题设置
    lookup_excel()
